﻿using System;
using System.Data;
using System.Collections.Generic;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
//using Oracle.DataAccess.Types;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace DBQuery
{
    /// <summary>
    /// Summary description for CustomerVisitTable
    /// </summary>
    public static class CustomerVisitTable
    {
        public static DataTable GetReminderList(bool bSeeAll)
        {
            DBLib dbl = new DBLib();
            String sxFilter = String.Empty;
            if (!bSeeAll)
            {
                sxFilter = "and ROWNUM < 6";
            }

            String sxQuery =
            @"
            select
                    T_CUSTOMER_VISIT_ID,
                    COMPANY_NAME,
                    NAME,
                    VISIT_DATETIME
                from
                    V_ICP_CUSTOMER_VISIT_LIST
                where
                    VISIT_STATUS = 'PLANNED'
                and
                    VISIT_DATETIME > sysdate
                and
                    VISIT_DATETIME < sysdate + 5"
                + sxFilter +
                @"order by
                    VISIT_DATETIME asc";
            DataTable dt = dbl.GetDataTable(sxQuery);
            
            return dt;
        }
        public static String GetReminderCounter()
        {
            DBLib dbl = new DBLib();

            String sxQuery =
            @"
            select
                count(*)
            from
                T_CUSTOMER_VISIT
            where
                VISIT_STATUS = 'PLANNED'
            and
                VISIT_DATETIME > sysdate
            and
                VISIT_DATETIME < sysdate + 5";

            String sxReturn = dbl.GetSingleValue(sxQuery).ToString();
            
            return sxReturn;
        }
        public static DateTime GetPlannedDateById(int iCVID)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iCVID;

            String sxQuery =
            @"
            select
                VISIT_DATETIME
            from
                T_CUSTOMER_VISIT
            where
                T_CUSTOMER_VISIT_ID = :prmID";
            DateTime dtmReturn = Convert.ToDateTime(dbl.GetSingleValue(sxQuery, prmID));
            
            return dtmReturn;
        }
        public static DataTable GetVisitByCarAndDate(String sxCarId, DateTime dtmDate)
        {
            DBLib dbl = new DBLib();

            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmCarId";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxCarId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmDate";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = dtmDate;

            String sxQuery =
            @"
            select
                v1.VISIT_DATETIME,
                v1.NAME CONTACT_NAME,
                v1.COMPANY_NAME,
                v1.CUSTOMER_ID,
                v1.VISIT_PURPOSE
            from
                V_ICP_CUSTOMER_VISIT_LIST v1
            left join
                V_ICP_CAR v2
            on
                v1.CUSTOMER_ID = v2.COMPANY_ID
            and
                v2.EMPLOYEE_NO = :prmCarId
            where
                v1.VISIT_DATETIME > :prmDate
            and
                v1.VISIT_DATETIME < :prmDate + 1
            and
                v1.VISIT_STATUS <> 'CANCELED'";
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            
            return dt;
        }
        public static int SaveVisit(CVData cvData)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "out_new_id";
            prmID.Direction = ParameterDirection.Output;
            prmID.OracleDbType = OracleDbType.Number;

            OracleParameter prmCPID = new OracleParameter();
            prmCPID.ParameterName = "in_CONTACT_PERSON_ID";
            prmCPID.Direction = ParameterDirection.Input;
            prmCPID.OracleDbType = OracleDbType.Number;
            if (cvData.iContactPersonID < 0)
            {
                prmCPID.Value = DBNull.Value;
            }
            else
            {
                prmCPID.Value = cvData.iContactPersonID;
            }

            OracleParameter prmNewCPID = new OracleParameter();
            prmNewCPID.ParameterName = "in_NEW_CONTACT_PERSON_ID";
            prmNewCPID.Direction = ParameterDirection.Input;
            prmNewCPID.OracleDbType = OracleDbType.Number;
            if (cvData.iNewContactPersonID < 0)
            {
                prmNewCPID.Value = DBNull.Value;
            }
            else
            {
                prmNewCPID.Value = cvData.iNewContactPersonID;
            }

            OracleParameter prmVisitDatetime = new OracleParameter();
            prmVisitDatetime.ParameterName = "in_VISIT_DATETIME";
            prmVisitDatetime.Direction = ParameterDirection.Input;
            prmVisitDatetime.OracleDbType = OracleDbType.Date;
            prmVisitDatetime.Value = cvData.dtmVisitDatetime;

            OracleParameter prmVisitStatus = new OracleParameter();
            prmVisitStatus.ParameterName = "in_VISIT_STATUS";
            prmVisitStatus.Direction = ParameterDirection.Input;
            prmVisitStatus.OracleDbType = OracleDbType.VarChar;
            prmVisitStatus.Value = cvData.sxVisitStatus;

            OracleParameter prmCreateBy = new OracleParameter();
            prmCreateBy.ParameterName = "in_CREATE_BY";
            prmCreateBy.Direction = ParameterDirection.Input;
            prmCreateBy.OracleDbType = OracleDbType.VarChar;
            prmCreateBy.Value = ICPUser.ID;

            OracleParameter prmVisitPurpose = new OracleParameter();
            prmVisitPurpose.ParameterName = "in_P_VISIT_PURPOSE_ID";
            prmVisitPurpose.Direction = ParameterDirection.Input;
            prmVisitPurpose.OracleDbType = OracleDbType.Number;
            prmVisitPurpose.Value = cvData.iP_Visit_Purpose_ID;

            String sxQuery = "pr_INSERT_T_CUSTOMER_VISIT";

            dbl.ExecuteProcedure(sxQuery, prmID, prmVisitDatetime, prmVisitStatus,
			prmCreateBy, prmVisitPurpose, prmCPID, prmNewCPID);

            Cached.CustomerVisitTable cachedCV = new Cached.CustomerVisitTable();
            cachedCV.RemoveCache();

            Cached.CARSpocTable cachedSpoc = new Cached.CARSpocTable();
            cachedSpoc.RemoveCache();
            
            cvData.iCVID = Convert.ToInt32(prmID.Value);
            return cvData.iCVID;
        }

        public static void EditVisitDatetime(int iCVID, DateTime dtmVisitDatetime)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iCVID;

            OracleParameter prmVisitDatetime = new OracleParameter();
            prmVisitDatetime.ParameterName = "prmVisitDatetime";
            prmVisitDatetime.OracleDbType = OracleDbType.Date;
            prmVisitDatetime.Value = dtmVisitDatetime;

            String sxQuery =
            @"
            update
                T_CUSTOMER_VISIT
            set
                VISIT_DATETIME = :prmVisitDatetime
            where
                T_CUSTOMER_VISIT_ID = :prmID
             ";

            dbl.ExecuteNonQuery(sxQuery, prmVisitDatetime, prmID);
            
        }

        public static void CancelVisit(int iCVID, String sxReason)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = iCVID;

            OracleParameter prmReason = new OracleParameter();
            prmReason.ParameterName = "prmReason";
            prmReason.OracleDbType = OracleDbType.VarChar;
            prmReason.Value = sxReason;

            OracleParameter prmUpdateBy = new OracleParameter();
            prmUpdateBy.ParameterName = "prmUpdateBy";
            prmUpdateBy.OracleDbType = OracleDbType.VarChar;
            prmUpdateBy.Value = ICPUser.ID;

            String sxQuery =
            @"
            update
                T_CUSTOMER_VISIT
            set
                CANCELATION_REASON = :prmReason,
                VISIT_STATUS = 'CANCELED',
                UPDATE_DATE = sysdate,
                UPDATE_BY = :prmUpdateBy
            where
                T_CUSTOMER_VISIT_ID = :prmID
             ";

            dbl.ExecuteNonQuery(sxQuery, prmReason, prmUpdateBy, prmID);
            
        }
        public static void UpdateVisitData(CVData cvData)
        {
            DBLib dbl = new DBLib();

            OracleParameter prmID = new OracleParameter();
            prmID.ParameterName = "prmID";
            prmID.OracleDbType = OracleDbType.Number;
            prmID.Value = cvData.iCVID;

            OracleParameter prmVisitStatus = new OracleParameter();
            prmVisitStatus.ParameterName = "prmVisitStatus";
            prmVisitStatus.OracleDbType = OracleDbType.VarChar;
            prmVisitStatus.Value = "REALIZED";

            OracleParameter prmUpdateBy = new OracleParameter();
            prmUpdateBy.ParameterName = "prmUpdateBy";
            prmUpdateBy.OracleDbType = OracleDbType.VarChar;
            prmUpdateBy.Value = ICPUser.ID;

            OracleParameter prmResult = new OracleParameter();
            prmResult.ParameterName = "prmResult";
            prmResult.OracleDbType = OracleDbType.VarChar;
            if (String.IsNullOrEmpty(cvData.sxResult))
            {
                prmResult.Value = DBNull.Value;
            }
            else
            {
                prmResult.Value = cvData.sxResult;
            }

            OracleParameter prmFollowUp = new OracleParameter();
            prmFollowUp.ParameterName = "prmFollowUp";
            prmFollowUp.OracleDbType = OracleDbType.VarChar;
            if (String.IsNullOrEmpty(cvData.sxFollowUp))
            {
                prmFollowUp.Value = DBNull.Value;
            }
            else
            {
                prmFollowUp.Value = cvData.sxFollowUp;
            }

            OracleParameter prmFileUploadID = new OracleParameter();
            prmFileUploadID.ParameterName = "prmFileUploadID";
            prmFileUploadID.OracleDbType = OracleDbType.Number;
            prmFileUploadID.Value = cvData.iFile_Upload_ID;

            OracleParameter prmCustomerExpectation = new OracleParameter();
            prmCustomerExpectation.ParameterName = "prmCustomerExpectation";
            prmCustomerExpectation.OracleDbType = OracleDbType.Number;
            prmCustomerExpectation.Value = cvData.iP_Customer_Expectation_ID;

            String sxQuery =
            @"
            update
                T_CUSTOMER_VISIT
            set
                VISIT_STATUS = :prmVisitStatus,
                UPDATE_BY = :prmUpdateBy,
                UPDATE_DATE = sysdate,
                RESULT = :prmResult,
                FOLLOW_UP = :prmFollowUp,
                T_FILE_UPLOAD_ID = :prmFileUploadID,
                P_CUSTOMER_EXPECTATION_ID = :prmCustomerExpectation
            where
                T_CUSTOMER_VISIT_ID = :prmID";

            dbl.ExecuteNonQuery(sxQuery, prmVisitStatus, prmUpdateBy, prmResult,
                prmFollowUp, prmFileUploadID, prmCustomerExpectation, prmID);

            Cached.CustomerVisitTable cachedCV = new Cached.CustomerVisitTable();
            cachedCV.RemoveCache();
            
        }
    }
}